/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dal;

import dal.util.Database;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.Users;

/**
 * this class interacts with database
 * @author gabriellebotbol
 */
public class UsersDAL 
{ 
    /**
     * return all users in the database
     * @return
     */
    public static List<Users> getAllUsers()
    {
        List<Users> users=new ArrayList<Users>();
        try
        {
            ResultSet rs=Database.executeQuery("select * from Users;");
            while(rs.next())
            {
                users.add(new Users(rs.getInt("idFriend"),rs.getString("username"),rs.getString("password"),rs.getString("firstname"),rs.getString("lastname"),null,rs.getString("mail"),rs.getInt("phone"),rs.getString("city"),rs.getString("country"),rs.getString("avatar"), rs.getString("visibility")));
            }
            rs.close();
            Database.closeConnection();
        } 
        catch (SQLException ex) 
        {
            Logger.global.log(Level.SEVERE, null, ex);
        }    
        return users;  
    }
    
    /**
     * return a user with the specified username
     * @param username
     * @return 
     */
    public static Users getUserByUsername(String username)
    {
        ResultSet rs=null;
        Users user=null;
        try 
        {
            
            rs=Database.executeQuery("select * from Users where username ='"+username+"';");
            if(rs.next())
            {
                user = new Users(rs.getInt("idFriend"),rs.getString("username"),rs.getString("password"),rs.getString("firstname"),rs.getString("lastname"),rs.getDate("birthdate"),rs.getString("mail"),rs.getInt("phone"),rs.getString("city"),rs.getString("country"),rs.getString("avatar"),rs.getString("visibility"));
            }
        }
        catch(SQLException ex)
        {
            Logger.global.log(Level.SEVERE, ex.getMessage());
        }
        finally
        {
            try 
            {
                Database.closeConnection();
            } 
            catch (SQLException ex) 
            {
                Logger.global.log(Level.SEVERE, null, ex);
            }
        }
        return user;
    }
    
    /**
     * returns a User with the specified mailaddress
     * @param mail
     * @return 
     */
    public static Users getUserByMail(String mail)
    {
        ResultSet rs=null;
        Users user=null;
        try 
        {
            
            rs=Database.executeQuery("select * from Users where mail ='"+mail+"';");
            if(rs.next())
            {
                user = new Users(rs.getInt("idFriend"),rs.getString("username"),rs.getString("password"),rs.getString("firstname"),rs.getString("lastname"),rs.getDate("birthdate"),rs.getString("mail"),rs.getInt("phone"),rs.getString("city"),rs.getString("country"),rs.getString("avatar"),rs.getString("visibility"));
            }
        }
        catch(SQLException ex)
        {
            Logger.global.log(Level.SEVERE, ex.getMessage());
        }
        finally
        {
            try 
            {
                Database.closeConnection();
            } 
            catch (SQLException ex) 
            {
                Logger.global.log(Level.SEVERE, null, ex);
            }
        }
        return user;
    }
    
    /**
     * deletes the specified user
     * @param user 
     */
    public static void deleteUser(Users user) 
    {
        try 
        {
            Database.executeNonQuery("delete from Users where username='"+user.getUsername()+"';");
        } 
        catch (SQLException ex) 
        {
            Logger.global.log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * insert a specified user in the database
     * @param user 
     */
    public static void insertUser(Users user)
    {
        try 
        {
            Database.executeNonQuery("INSERT INTO Friends(username) VALUES('"+user.getUsername()+"');");
            StringBuilder builder=new StringBuilder();
            builder.append("INSERT INTO Users VALUES('");
            builder.append(user.getUsername());
            builder.append("','");
            builder.append(user.getPassword());
            builder.append("','");
            builder.append(user.getFirstname());
            builder.append("','");
            builder.append(user.getLastname());
            builder.append("','");
            builder.append(user.getBirthdate());
            builder.append("','");
            builder.append(user.getMail());
            builder.append("',");
            builder.append(user.getPhone());
            builder.append(",'");
            builder.append(user.getCity());
            builder.append("','");
            builder.append(user.getCountry());
            builder.append("','");
            builder.append(user.getAvatar());
            builder.append("',");
            builder.append((FriendsDAL.getFriendByUsername(user.getUsername())).getIdFriends());
            builder.append(",'");
            builder.append(user.getVisibility());
            builder.append("')");
            
            Database.executeNonQuery(builder.toString());
        } 
        catch (SQLException ex) 
        {
            Logger.global.log(Level.SEVERE, null, ex);
        }        
    }
    
    /**
     * updates a specified user
     * @param user 
     */
    public static void updateUser(Users user)
    {
        try 
        {
            StringBuilder builder=new StringBuilder();
            builder.append("UPDATE Users SET username='");
            builder.append(user.getUsername());
            builder.append("', password='");
            builder.append(user.getPassword());
            builder.append("',firstname='");
            builder.append(user.getFirstname());
            builder.append("',lastname='");
            builder.append(user.getLastname());
            builder.append("',birthdate='");
            builder.append(user.getBirthdate());
            builder.append("',mail='");
            builder.append(user.getMail());
            builder.append("',phone=");
            builder.append(user.getPhone());
            builder.append(",city='");
            builder.append(user.getCity());
            builder.append("',country='");
            builder.append(user.getCountry());
            builder.append("',avatar='");
            builder.append(user.getAvatar());
            builder.append("',visibility='");
            builder.append(user.getVisibility());
            builder.append("' where username ='");
            builder.append(user.getUsername());
            builder.append("';");
            
            Database.executeNonQuery(builder.toString());
        } 
        catch (SQLException ex) 
        {
            Logger.global.log(Level.SEVERE, null, ex);
        }        
    }
    
}
